﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Collections;

namespace SAP
{
    public partial class P_PQR : System.Web.UI.Page
    {

        private static string GeneralID = "";
        private static string dbType;

        private static int WSCID = -1;
        private static int QPRID = -1;
        private static string VIN = "";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                dbType = Session[VSM.Utils.AppConstants.ID_Dealer].ToString() == "VSM" ? VSM.Utils.AppConstants.VSM : VSM.Utils.AppConstants.DEALER;
                GeneralID = Request.QueryString["GeneralID"] == null ? "" : Request.QueryString["GeneralID"];
                VIN = Request.QueryString["VIN"] == null ? "" : Request.QueryString["VIN"];
                hf_GeneralID.Value = GeneralID.ToString();
                 //loadDropdownList();

                 loadQPR();
            }
        }

        private void generateSeqNo()
        {
            DateTime currDate = DateTime.Now;
            string sql = " select RIGHT('000' + cast((count(QPRID)) as nvarchar(10)), 4) as NewSeqNo from U_QPR qp join U_WSC ws on qp.WSCID = ws.WSCID ";
            sql += " join U_GeneralService gs on ws.GeneralID = gs.GeneralID ";
            sql += " where gs.DealerCode = '" + Session[VSM.Utils.AppConstants.ID_Dealer] + "'";
            sql += " and YEAR(gs.ServiceDate) = " + currDate.Year;

            Services sv = new Services();
            DataSet ds = sv.ExecuteQuery("DEALER", sql);
            txt_SeqNo.Text = ds.Tables[0].Rows[0]["NewSeqNo"].ToString();
        }

        private void loadQPR()
        {
            string sql = "SELECT wsc.[WSCID],gs.[GeneralID],wsc.[RegistrationNum],wsc.[DisCode],wsc.[DisName],wsc.[DealerCode],wsc.[DealerName] ";
            sql += " ,qpr.[SeqNo],[InvoiceDate],wsc.[S],wsc.[ID],wsc.[T],wsc.[DIV],[ConditionCode],[A],[B],[C],gs.[CustomerName],gs.[VIN],[FailureDate],[KD], ";
            sql += " [SoldDate],[HourMeter],[Manu],[RefCode],[M],[COM],[RatioL],[RatioP],[FCT],[Remarks],wsc.[Status], gs.Model, EngineNumber,  ";
            sql += "  ServiceDate, Odometer, gs.Address, isnull(qpr.QPRID, -1) QPRID,  ";
            sql += " [RearBody],[VehicleSpeed],[EngineSpeed],[RoadCondition],[LoadCondition],[Subject],[UsePurpose], ";
            sql += "  [Country],[WeatherTemp],isnull([Maintenance], 0) Maintenance, isnull([Frequency], 0) Frequency, [Destination], ";
            sql += "  [PossibleCause],[ActionTaken],[Additional],[VSMComment], qpr.[Status] QPRStatus";
            sql += "  FROM [U_WSC] wsc join U_GeneralService gs on wsc.GeneralID = gs.GeneralID ";
            sql += " left join [U_QPR] qpr on qpr.WSCID = wsc.WSCID ";
            sql += "  where wsc.GeneralID = " + GeneralID.ToString();

            Services sv = new Services();
            DataSet ds = sv.ExecuteQuery("DEALER", sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                WSCID = Int32.Parse(dr["WSCID"].ToString());
                QPRID = Int32.Parse(dr["QPRID"].ToString());
                txtDate.Text = FormatDate(dr["ServiceDate"].ToString());
                txt_Odometer.Text = dr["Odometer"].ToString();
                txt_DealerCode.Text = dr["DealerCode"].ToString();
                txt_DealerName.Text = dr["DealerName"].ToString();
                txt_SeqNo.Text = dr["SeqNo"].ToString();

                txt_Model.Text = dr["Model"].ToString();
                txt_EngineNum.Text = dr["EngineNumber"].ToString();

                txt_PartNo.Text = dr["ConditionCode"].ToString();

                txt_CusName.Text = dr["CustomerName"].ToString();
                txt_Address.Text = dr["Address"].ToString();
                txt_VIN.Text = dr["VIN"].ToString();

                txt_FailureDate.Text = FormatDate(dr["FailureDate"].ToString());
                txt_SoldDate.Text = FormatDate(dr["SoldDate"].ToString());
            
                txt_RearBody.Text = dr["RearBody"].ToString();
                txt_VehicleSpeed.Text = dr["VehicleSpeed"].ToString();
                txt_EngineSpeed.Text = dr["EngineSpeed"].ToString();
                txt_RoadCondition.Text = dr["RoadCondition"].ToString();
                txt_LoadCondition.Text = dr["LoadCondition"].ToString();
                txt_Use.Text = dr["UsePurpose"].ToString();
                txt_Country.Text = dr["Country"].ToString();
                txt_Weather.Text = dr["WeatherTemp"].ToString();
                rbl_Maintenance.SelectedIndex = Int32.Parse( dr["Maintenance"].ToString());
                rbl_Frequency.SelectedIndex = Int32.Parse( dr["Frequency"].ToString());
                txt_Subject.Text = dr["Subject"].ToString();
                txt_Destination.Text = dr["Destination"].ToString();
                txt_PossibleCause.Text = dr["PossibleCause"].ToString();
                txt_Actiontaken.Text = dr["ActionTaken"].ToString();
                txt_AdditionalComments.Text =dr["Additional"].ToString();
                txt_VSMComments.Text = dr["VSMComment"].ToString();
                if (txt_SeqNo.Text == "")
                    generateSeqNo();
                loadAttachment();
            }
        }

        private string FormatDate(string dateIn)
        {
            if (dateIn.Trim() == "")
                return "";
            DateTime pars = DateTime.Parse(dateIn);
            return pars.ToString("dd/MM/yyyy");

        }

        private void loadDefaultNew(string VIN)
        {
            //get dealer Name
            Services sv = new Services();
            string sqlDealer = "select CardCode, CardName from OCRD where CardCode = '" + Session[VSM.Utils.AppConstants.ID_Dealer] + "'";
            DataSet dsDealer = sv.ExecuteQuery("VSM", sqlDealer);
            if (dsDealer.Tables[0].Rows.Count > 0)
            {
                txt_DealerCode.Text = dsDealer.Tables[0].Rows[0]["CardCode"].ToString();
                txt_DealerName.Text = dsDealer.Tables[0].Rows[0]["CardName"].ToString();
            }

            string sql = " select inv.DocDate, rn.DistNumber as VIN, rn.Notes as EngineNo, ms.Name as ModelName, ";
            sql += " cg.RegistrationNumber, inv.CardCode, inv.CardName ";
            sql += " from OINV inv join INV1 inv1 on inv.DocEntry = inv1.BaseEntry ";
            sql += " join  OSRN rn on inv1.ItemCode = rn.ItemCode ";
            sql += " join OITM it on it.ItemCode = inv1.ItemCode ";
            sql += " join [@ModelSales] ms on ms.Code = it.U_ModelSales ";
            sql += " left join U_CustomerRegis cg on cg.VIN = rn.DistNumber ";
            sql += " where rn.DistNumber = '" + VIN + "'";

            DataSet dsInfo = sv.ExecuteQuery("VSM", sql);

            if (dsInfo.Tables[0].Rows.Count > 0)
            {
                txt_CusName.Text = dsInfo.Tables[0].Rows[0]["CardName"].ToString();
                txt_VIN.Text = dsInfo.Tables[0].Rows[0]["VIN"].ToString();
                txt_Model.Text = dsInfo.Tables[0].Rows[0]["ModelName"].ToString();
                txt_EngineNum.Text = dsInfo.Tables[0].Rows[0]["EngineNo"].ToString();
            }

            DateTime currDate = DateTime.Now;
            string sqlSeqNo = "select RIGHT('000' + cast((count(GeneralID)) as nvarchar(10)), 4) as NewSeqNo from U_GeneralService ";
            sqlSeqNo += " where JobKind = '004' and DealerCode = '" + Session[VSM.Utils.AppConstants.ID_Dealer] + "' and year(ServiceDate) = " + currDate.Year;
            DataSet dsSeq = sv.ExecuteQuery("DEALER", sqlSeqNo);
            if (dsSeq.Tables[0].Rows.Count > 0)
            {
                txt_SeqNo.Text = dsSeq.Tables[0].Rows[0]["NewSeqNo"].ToString();
            }
            //string sqlSeq = "select RIGHT('0000' + cast((isnull(max(SeqNo), 0) + 1)as nvarchar(10)), 4) NextNumber from U_WSC where DealerCode = '" + Session[VSM.Utils.AppConstants.ID_Dealer] + "'";
            //DataSet dsSeq = sv.ExecuteQuery("DEALER", sqlSeq);
            //txt_SeqNo.Text = dsSeq.Tables[0].Rows[0]["NextNumber"].ToString();

            string sqlOdo = "select Odometer, ServiceDate from U_GeneralService where GeneralID = " + GeneralID.ToString();
            DataSet dsOdo = sv.ExecuteQuery("DEALER", sqlOdo);
            if (dsOdo.Tables[0].Rows.Count > 0)
            {
                txt_Odometer.Text = dsOdo.Tables[0].Rows[0]["Odometer"].ToString();
                txtDate.Text = FormatDate(dsOdo.Tables[0].Rows[0]["ServiceDate"].ToString());
            }
        }

        private void loadAttachment() {
            string sql = "select AttID, QPRID, Title, FileName, Size from U_QPRAttach where QPRID = " + QPRID;
            Services sv = new Services();
            DataTable dt = sv.ExecuteQuery("DEALER", sql).Tables[0];
            if (dt.Rows.Count > 0) {
                hf_AttID1.Value = dt.Rows[0]["AttID"].ToString();
                txt_Title1.Text = dt.Rows[0]["Title"].ToString();
                txt_FileName1.Text = dt.Rows[0]["FileName"].ToString();
                txt_FileSize1.Text = dt.Rows[0]["Size"].ToString();
            }
            if (dt.Rows.Count > 1)
            {
                hf_AttID2.Value = dt.Rows[1]["AttID"].ToString();
                txt_Title2.Text = dt.Rows[1]["Title"].ToString();
                txt_FileName2.Text = dt.Rows[1]["FileName"].ToString();
                txt_FileSize2.Text = dt.Rows[1]["Size"].ToString();
            }
            if (dt.Rows.Count > 2)
            {
                hf_AttID3.Value = dt.Rows[2]["AttID"].ToString();
                txt_Title3.Text = dt.Rows[2]["Title"].ToString();
                txt_FileName3.Text = dt.Rows[2]["FileName"].ToString();
                txt_FileSize3.Text = dt.Rows[2]["Size"].ToString();
            }
            if (dt.Rows.Count > 3)
            {
                hf_AttID4.Value = dt.Rows[3]["AttID"].ToString();
                txt_Title4.Text = dt.Rows[3]["Title"].ToString();
                txt_FileName4.Text = dt.Rows[3]["FileName"].ToString();
                txt_FileSize4.Text = dt.Rows[3]["Size"].ToString();
            }
            if (dt.Rows.Count > 4)
            {
                hf_AttID5.Value = dt.Rows[4]["AttID"].ToString();
                txt_Title5.Text = dt.Rows[4]["Title"].ToString();
                txt_FileName5.Text = dt.Rows[4]["FileName"].ToString();
                txt_FileSize5.Text = dt.Rows[4]["Size"].ToString();
            }
        }

        private void saveAttachment() {
            string dealerCode = Session[VSM.Utils.AppConstants.ID_Dealer].ToString();
            string filePath1 = "";
            string filePath2 = "";
            string filePath3 = "";
            string filePath4 = "";
            string filePath5 = "";
            Services sv = new Services();
            DataTable dt = new DataTable();
            dt.Columns.Add("AttID");
            dt.Columns.Add("QPRID");
            dt.Columns.Add("Title");
            dt.Columns.Add("FileName");
            dt.Columns.Add("Size");
            if (FileUploadControl_1.HasFile) {
                DataRow dr = dt.NewRow();
                string fileName1 = dealerCode + "-" + QPRID + "-" + txt_FileName1.Text;
                dr["AttID"] = -1;
                dr["QPRID"] = QPRID;
                dr["Title"] = txt_Title1.Text;
                dr["FileName"] = fileName1;
                dr["Size"] = txt_FileSize1.Text;
                dt.Rows.Add(dr);
                filePath1 = System.IO.Path.Combine( Server.MapPath("Services"), fileName1);
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                sv.InsertAttachment("DEALER", ds);
            }
            
        }

        protected void btn_Save_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("WSCID");
            dt.Columns.Add("SeqNo");
            dt.Columns.Add("RearBody");
            dt.Columns.Add("VehicleSpeed");
            dt.Columns.Add("EngineSpeed");
            dt.Columns.Add("RoadCondition");
            dt.Columns.Add("LoadCondition");
            dt.Columns.Add("Subject");
            dt.Columns.Add("UsePurpose");
            dt.Columns.Add("Country");
            dt.Columns.Add("WeatherTemp");
            dt.Columns.Add("Maintenance");
            dt.Columns.Add("Frequency");
            dt.Columns.Add("Destination");
            dt.Columns.Add("PossibleCause");
            dt.Columns.Add("ActionTaken");
            dt.Columns.Add("Additional");
            dt.Columns.Add("VSMComment");
            dt.Columns.Add("Status");
            dt.Columns.Add("QPRID");

            DataRow dr = dt.NewRow();
            dr["WSCID"] = WSCID;
            dr["SeqNo"] = txt_SeqNo.Text;
            dr["RearBody"] = txt_RearBody.Text;
            dr["VehicleSpeed"] = txt_VehicleSpeed.Text;
            dr["EngineSpeed"] = txt_EngineSpeed.Text;
            dr["RoadCondition"] = txt_RoadCondition.Text;
            dr["LoadCondition"] = txt_LoadCondition.Text;
            dr["Subject"] = txt_Subject.Text;
            dr["UsePurpose"] = txt_Use.Text;
            dr["Country"] = txt_Country.Text;
            dr["WeatherTemp"] = txt_Weather.Text;
            dr["Maintenance"] = rbl_Maintenance.SelectedIndex;
            dr["Frequency"] = rbl_Frequency.SelectedIndex;
            dr["Destination"] = txt_Destination.Text;
            dr["PossibleCause"] = txt_PossibleCause.Text;
            dr["ActionTaken"] = txt_Actiontaken.Text;
            dr["Additional"] = txt_AdditionalComments.Text;
            dr["VSMComment"] = txt_VSMComments.Text;
            dr["Status"] = 0;
            dr["QPRID"] = QPRID;
            dt.Rows.Add(dr);

            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            Services sv = new Services();
            if (QPRID == -1)
            {
                QPRID = sv.InsertQPR(dbType, ds);
                saveAttachment();
                if (QPRID != -1)
                {
                    lb_Message.Text = "[Add New] Operation complete.";
                }
                else
                    lb_Message.Text = "[Add New] Operation failure.";
            }
            else
            {
                int count = sv.UpdateQPR(dbType, ds);
                saveAttachment();
                if (count != -1)
                    lb_Message.Text = "[Update] Operation complete.";
                else
                    lb_Message.Text = "[Update] Operation failure.";
            }
    
        }
    }
}